package net.hn.hnms.biz.sewage.dao;

import net.hn.hnms.biz.sewage.entity.DomesticSewageAmmoniaNitrogenEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
 * @author 智呈物联 nzw
 * @date 2022/9/23
 */
public interface DomesticSewageAmmoniaNitrogenMapper extends JpaRepository<DomesticSewageAmmoniaNitrogenEntity,Long> {

    List<DomesticSewageAmmoniaNitrogenEntity> findAllByShiftId(Long shiftId);

    void deleteByShiftId(Long shiftId);


    @Query(value = "SELECT\n" +
            "\tsi.fill_in_time AS \"time\",\n" +
            "\tCOALESCE(SUM ( dss.aluminum_chloride ),0.00) AS aluminumChloride,\n" +
            "\tCOALESCE(SUM ( dss.polyacrylamide ),0.00) AS polyacrylamide,\n" +
            "\tCOALESCE(SUM ( CAST ( dss.sewage_treatment AS NUMERIC ( 10, 2 ) ) ),0.00) AS sewageTreatment,\n" +
            "\tCOALESCE(SUM ( CAST ( dss.reuse AS NUMERIC ( 10, 2 ) ) ),0.00) AS reuse,\n" +
            "\tCOALESCE(SUM ( dss.disinfector ),0.00) AS disinfector,\n" +
            "\tCOALESCE(SUM ( dss.spfs ),0.00) AS spfs,\n" +
            "\tCOALESCE(SUM ( dso.cod_value ),0.00) AS cod \n" +
            "FROM\n" +
            "\tdomestic_sewage_shift dss\n" +
            "\tLEFT JOIN sewage_index si on si.\"id\" = dss.index_id \n" +
            "\tLEFT JOIN domestic_sewage_ods dso ON dss.\"id\" = dso.shift_id \n" +
            "WHERE\n" +
            " si.sewage_type = '1' \n" +
            "\tAND to_char( si.fill_in_time, 'yyyy-MM' ) = ?1\n" +
            "\tand CASE when ?2 != '' then trim(si.mine_code) = ?2 else 1=1 end \n" +
            "GROUP BY\n" +
            "\tsi.fill_in_time \n" +
            "ORDER BY\n" +
            "\tsi.fill_in_time",nativeQuery = true)
    List<Map<String,Object>> findByMonth(String month,String mineCode);


    @Query(value = "SELECT\n" +
            "\tSUM ( dss.aluminum_chloride ) AS aluminumChloride,\n" +
            "\tSUM ( dss.polyacrylamide ) AS polyacrylamide,\n" +
            "\tSUM ( CAST ( dss.sewage_treatment AS NUMERIC ( 10, 2 ) ) ) AS sewageTreatment,\n" +
            "\tSUM ( CAST ( dss.reuse AS NUMERIC ( 10, 2 ) ) ) AS reuse,\n" +
            "\tSUM ( dss.disinfector ) AS disinfector,\n" +
            "\tSUM ( dss.spfs ) AS spfs,\n" +
            "\tSUM ( dso.cod_value ) AS cod \n" +
            "FROM\n" +
            "\tdomestic_sewage_shift dss\n" +
            "\tLEFT JOIN sewage_index si on si.\"id\" = dss.index_id \n" +
            "\tLEFT JOIN domestic_sewage_ods dso ON dss.\"id\" = dso.shift_id \n" +
            "WHERE\n" +
            " si.sewage_type = '1' \n" +
            "\tAND to_char( si.fill_in_time, 'yyyy-MM' ) = ?1\n" +
            "\tand CASE when ?2 != '' then trim(si.mine_code) = ?2 else 1=1 end ",nativeQuery = true)
    Map<String,Object> findCountMonth(String month,String mineCode);

}
